package cn.example.freid.util;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * For JDBC Database Operation
 * @author Freid
 * 
 */
public class JDBCUtil {
	
	private Connection connection = null;
	private Statement statement = null;
	private PreparedStatement preparedStatement = null;
	
	/**
	 * @param url
	 * @param user
	 * @param password
	 * @throws InstantiationException
	 * @throws IllegalAccessException
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 * @throws IOException 
	 */
	public JDBCUtil(String url, String user, String password, boolean autoCommit) throws 
	InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException, IOException {
		
		Class.forName(PropertiesUtil.get("dbdriver")).newInstance();
		connection = DriverManager.getConnection(url, user, password);
		connection.setAutoCommit(autoCommit);
		statement = connection.createStatement();
	}
	
	public JDBCUtil() throws SQLException, IOException, IllegalAccessException, 
	InstantiationException, ClassNotFoundException {
		
		Class.forName(PropertiesUtil.get("dbdriver")).newInstance();
		connection = DriverManager.getConnection(PropertiesUtil.get("dbURL"), 
				PropertiesUtil.get("username"), PropertiesUtil.get("password"));
		connection.setAutoCommit(true);
		statement = connection.createStatement();
	}
	
	/**
	 * commit for up, make sure call this function to commit update 
	 * after all the operation if not auto commit.
	 * @throws SQLException
	 */
	public void commit() throws SQLException {
		
		connection.commit();
	}
	
	/**
	 * rollback if update failed, call this function in catch block.
	 * @throws SQLException
	 */
	public void rollback() throws SQLException {
		
		connection.rollback();
	}
	
	/**
	 * excute, using Statement.execute().
	 * @param sql
	 * @return
	 * @throws SQLException
	 */
	public boolean excute(String sql) throws SQLException {
		
		boolean result = statement.execute(sql);
		return result;
	}
	
	/**
	 * excute, using Preparedstatement.execute(), set args with method setObject().
	 * @param sql
	 * @param args
	 * @return
	 * @throws SQLException
	 */
	public boolean excute(String sql, Object... args) throws SQLException {

		preparedStatement = connection.prepareStatement(sql);
		for (int i = 1; i <= args.length; i++) {
			
			preparedStatement.setObject(i, args[i-1]);
		}
		
		return preparedStatement.execute();
 	}
	
	/**
	 * update, using Statement.executeUpdate().
	 * @param sql
	 * @return
	 * @throws SQLException
	 */
	public int update(String sql) throws SQLException {

		return statement.executeUpdate(sql);
 	}
	
	/**
	 * update, using Preparedstatement.execute(), set args with method setObject().
	 * @param sql
	 * @param args
	 * @return
	 * @throws SQLException
	 */
	public int update(String sql, Object... args) throws SQLException {

		preparedStatement = connection.prepareStatement(sql);
		for (int i = 1; i <= args.length; i++) {
			
			preparedStatement.setObject(i, args[i-1]);
		}
		
		return preparedStatement.executeUpdate();
 	}
	
	/**
	 * query, using Statement.executeQuery().
	 * @param sql
	 * @return
	 * @throws SQLException
	 */
	public ResultSet query(String sql) throws SQLException {
		
		return statement.executeQuery(sql);
	}
	
	/**
	 * update, using Preparedstatement.executeQuery(), set args with method setObject().
	 * @param sql
	 * @param args
	 * @return
	 * @throws SQLException
	 */
	public ResultSet query(String sql, Object... args) throws SQLException {
		
		preparedStatement = connection.prepareStatement(sql);
		for (int i = 1; i <= args.length; i++) {
			
			preparedStatement.setObject(i, args[i-1]);
		}
		
		return preparedStatement.executeQuery();
	}
	
	/**
	 * close connection, statement and preparedstatement.
	 * @throws SQLException
	 */
	public void closeConAndState() throws SQLException {
		
		if (connection != null) {
			connection.close();
			connection = null;
		}
		if (statement != null) {
			statement.close();
			statement = null;
		}
		if (preparedStatement != null) {
			preparedStatement.close();
			preparedStatement = null;
		}
	}
	
}
